package com.garen.common;


import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.garen.orm.OrmDBType;
import com.garen.orm.OrmDBTypeFactory;
import com.garen.orm.OrmTable;
import com.garen.utils.MapBuilder;

/*
 * 抽象持久层
 * 负责与数据库交互
 * 负责表与实体类的映射
 * */
public abstract class AbstractDao<T> implements IBaseDao<T>{

	private NamedParameterJdbcTemplate jdbcTemp = null;
	
	private JdbcTemplate jdbcTemp0 = null;
	
	protected  Log log = LogFactory.getLog(this.getClass());

	private OrmDBType ormdb;

	private OrmTable<T> ot = null;
	
	protected OrmTable<T> getOrmTable() {
		return ot;
	}
	//sql生成page语句
	public static String createPageSql(String sql){
		String [] sqls = sql.split("\\s+");
		int flag = 0;
		StringBuilder newSql = new StringBuilder("select count(1) from");
		for(String s :sqls){
			if(flag == -1){
				newSql.append(" " + s);
			}else{
				if("select".equals(s) || "(select".equals(s)) flag++;
				else if("from".equals(s)) {
					if(1 == flag){
						flag = -1;
					}else flag--;
				}
			}
		}
		return newSql.toString();
	}
	//实体类映射初始化
	protected void initOrm(Class<?> myclass) {
		ormdb = OrmDBTypeFactory.create(jdbcTemp0.getDataSource());
		OrmTable<T> ormt = new OrmTable<T>(ormdb);
		String className = myclass.getName();
		if(ormt.init(myclass)) {
			log.debug("实体类-关系映射成功." + className);
			ot = ormt;
		}
		else{
			log.debug("实体类-关系映射失败." + className);
		}
	}

	//查询结果为Integer 类型，结果为空时，返回null
	public Integer queryForInt(String sql,Map<String,Object> paramMap) {
		Integer ret  = null;
		try{
			ret =  queryForObject(sql, paramMap, Integer.class);
		}catch(EmptyResultDataAccessException  ex){}
		return ret;
	}
	
	//批量保存实体
	@Override
	public int saveAll(List<T> objlist){
		for(T obj : objlist){
			save(obj);
		}
		return 0;
	}
	
	//保存实体,根据主键判断新增还是更新
	@Override
	public int save(T obj){
		int ret = 0;
		if(ot.idIsNull(obj))
			ret = add(obj);
		else
			ret = update(obj);
		return ret;
	}
	
	private int add(T obj,boolean nullFlag) {
		Map<String,Object> paramMap = ot.createSql(obj, nullFlag);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.INSERT_KEY).toString();
		return update(sql, paramMap);
	}

	/*
	 * 新建
	 * 返回改变的行数
	 */
	@Override
	public int add(T obj){
		return add(obj,true);
	}
	
	/*
	 * 新建
	 * 返回改变的行数
	 */
	@Override
	public int addWithNull(T obj){
		return add(obj,false);
	}
	
	/*
	 * 新建,自增主键写入实体类中
	 * 返回改变的行数
	 * nullFlag true忽略为Null的字段
	 */
	@Override
	public int addAndId(T obj){
		int ret = add(obj,true);
		ot.putIdValue(obj, getAutoId());//获取主键
		return ret; 
	}
	
	/*
	 * 新建,自增主键写入实体类中
	 * 返回改变的行数
	 * nullFlag true忽略为Null的字段
	 */
	@Override
	public int addAndIdWithNull(T obj){
		int ret = add(obj,false);
		ot.putIdValue(obj, getAutoId());
		return ret;
	}
	
	//获取自增主键
	protected Integer getAutoId(){
		Integer id = null;
		if(ormdb.isMysql()){
			String sql = "select last_insert_id()";
			id =  queryForObject(sql, new HashMap<String,Object>(),Integer.class);
		}else if(ormdb.isOracle()){//序列获取
			String sequence = ot.getIdSequence();
			if(StringUtils.isNotEmpty(sequence)){
				String sql = "select " + ot.getSchemaValue() + sequence + ".nextval from dual ";
				id =  queryForObject(sql, new HashMap<String,Object>(),Integer.class);
			}
		}else if(ormdb.isMssql()){
			String sql = " SELECT @@IDENTITY ";
			id =  jdbcTemp.queryForObject(sql, new HashMap<String,Object>(),Integer.class);
		}
		return id;
	}

	
	//主键做为条件,更新不为null字段
	@Override
	public int update(T obj) {
		Map<String,Object> paramMap = ot.updateSql(obj, true);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.UPDATE_KEY).toString();
		return update(sql, paramMap);
	}

	@Override
	public int updateWithNull(T obj) {
		Map<String,Object> paramMap = ot.updateSql(obj, false);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.UPDATE_KEY).toString();
		return update(sql, paramMap);
	}
	
	
	//field字段不为Null 做为where条件
	@Override
	public int updateByField(T obj, String field) {
		if(StringUtils.isEmpty(field)) {
			log.warn("field字段不能为空.");
			return -1;
		}
		Map<String,Object> paramMap = ot.updateSqlWithField(obj, true, field.toUpperCase());
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.UPDATE_KEY).toString();
		return update(sql, paramMap);
	}
	
	//field字段不为Null 做为where条件
	@Override
	public int updateByFieldWithNull(T obj, String field) {
		if(StringUtils.isEmpty(field)) {
			log.warn("field字段不能为空.");
			return -1;
		}
		Map<String,Object> paramMap = ot.updateSql(obj, true);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.UPDATE_KEY).toString();
		return update(sql, paramMap);
	}

	@Override
	public int del(T obj) {
		Map<String,Object> paramMap = ot.delSQL(obj);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.DELETE_KEY).toString();
		return update(sql, paramMap);
	}

	@Override
	public int del(Integer id, Class<T> myclass) {
		Map<String,Object> paramMap = ot.delByIdSQL(id);
		if(paramMap == null){
			log.warn("生成SQL错误..");
			return -2;
		}
		String sql = paramMap.remove(OrmTable.DELETE_KEY).toString();
		return update(sql, paramMap);
	}

	@Override
	public T get(Integer id, Class<T> myclass) {
		try {
			T obj = myclass.newInstance();
			if(ot.putIdValue(obj, id))//设置主键
				return get(obj);
		} catch (InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public T get(T obj) {
		List<T> list = getList(obj,null);
		if(list.size() > 0) return list.get(0);
		else return null;
	}
	
	@Override
	public List<T> getList() {
		return getList(null, null);//默认排序
	}
	
	@Override
	public List<T> getList(T obj) {
		return getList(obj, null);//默认排序
	}

	/*
	 * 查询列表
	 */
	@Override
	public List<T> getList(T obj,String orderBy){
		Map<String,Object> paramMap = ot.getListSQL(null,null,obj,orderBy);
		String sql = paramMap.remove(OrmTable.SELECT_KEY).toString();
		return  query(sql, paramMap, ot);
	}
	
	//默认排序,分页查询
	@Override
	public List<T> getPageList(PageBean<T> pb, T obj) {
		pb.setRows(getPageList(pb, obj, null));
		return  pb.getRows();
	}
	
	/*
	 * 查询列表
	 */
	@Override
	public List<T> getPageList(PageBean<T> pb,T obj,String orderBy){
		if(pb.getIsPage() == false){
			pb.setRows(getList(obj, orderBy));
			pb.setTotal(pb.getRows().size());
			return pb.getRows();//不分页
		}
		Map<String,Object> paramMap = ot.getPageList(pb,obj,null,null,orderBy);
		String sql = (String)paramMap.remove(OrmTable.SELECT_KEY);
		String pagesql = (String)paramMap.remove(OrmTable.SELECT_PAGE_KEY);
		//设置记录数
		pb.setTotal(queryForInt(pagesql, paramMap));
		pb.setRows(query(sql, paramMap, ot));
		return  pb.getRows();
	}
	
	/*===================继承函数，用户扩展查询 !=====================*/
	/*
	 * 查询列表,附加语句及参数
	 */
	protected List<T> getList(String addParams,Map<String,Object> addMap,
			T obj,String orderBy){
		Map<String,Object> paramMap = ot.getListSQL(addParams,addMap,obj,orderBy);
		String sql = paramMap.remove(OrmTable.SELECT_KEY).toString();
		return  query(sql, paramMap, ot);
	}
	
	/*
	 * 功能继承List<T> getPageList(PageBean pb,T obj);
	 * 加入附加where条件
	 * 可分页
	 */
	protected List<T> getPageList(PageBean<T> pb,String whereSql,
			Map<String,Object> addMap,T obj,String orderBy){
		if(pb.getIsPage() == false){
			pb.setRows(getList(obj, orderBy));
			pb.setTotal(pb.getRows().size());
			return pb.getRows();//不分页
		}
		Map<String,Object> paramMap = ot.getPageList(pb,obj,whereSql,addMap,orderBy);
		String sql = (String)paramMap.remove(OrmTable.SELECT_KEY);
		String pagesql = (String)paramMap.remove(OrmTable.SELECT_PAGE_KEY);
		//设置记录数
		pb.setTotal(queryForInt(pagesql, paramMap));
		pb.setRows(query(sql, paramMap, ot));
		return  pb.getRows();
	}
	
	/*
	 * 自定义查询列表
	 */
	protected List<T> getListBySQL(String sql,Map<String,Object> paramMap){
		if(paramMap == null) paramMap = new HashMap<String,Object>();
		return  query(sql, paramMap, ot);
	}
	
	/*
	 * 自定义查询列表
	 */
	protected List<T> getListBySQL(String sql,Map<String,Object> paramMap,
			final Map<String,Object> resultMap){
		return  query(sql, paramMap, new RowMapper<T>() {
			@SuppressWarnings("deprecation")
			@Override
			public T mapRow(ResultSet rs, int arg1) throws SQLException {
				OrmTable<T> ot = getOrmTable();
				T obj = ot.mapRowFilterIn(rs, arg1, "");
				for(String key : resultMap.keySet()){
					Object val = rs.getObject(key);
					try {
						PropertyUtils.setProperty(obj, resultMap.get(key).toString(), val);
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
					}
				}
				return obj;
			}
		});
	}
	
	/*
	 * 查询无分页列表
	 * 自定义列
	 * sql中自定义排序
	 */
	protected List<T> getListBySQL(String sql,Map<String,Object> paramMap,
			RowMapper<T> rowMapper){
		return  query(sql, paramMap, rowMapper);
	}
	
/*=========================分页查询==============================
 * 
 * 分页才需要自定义排序
 * =================================================
 * */	
	/*
	 * 分页自定义查询列表
	 */
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			Map<String,Object> paramMap){
		String pagesql = createPageSql(sql);
		return  getPageListBySQL(pb,sql,pagesql, paramMap);
	}
	
	/*
	 * 分页自定义查询列表,自定义排序
	 */
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			Map<String,Object> paramMap,String orderBy){
		String pagesql = createPageSql(sql);
		pb.setTotal(queryForInt(pagesql, paramMap));
		StringBuilder sb = new StringBuilder(sql);
		ot.addPageSql(sb, pb, orderBy);
		pb.setRows(query(sb.toString(), paramMap, ot));
		return  pb.getRows();
	}
	
	/*
	 * 查询分页列表，自定义分页语句
	 */
	@Deprecated
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			String pagesql,Map<String,Object> paramMap){
		pb.setTotal(queryForInt(pagesql, paramMap));
		StringBuilder sb = new StringBuilder(sql);
		ot.addPageSql(sb, pb, null);
		sql = sb.toString();
		pb.setRows(query(sql, paramMap, ot));
		return  pb.getRows();
	}
	
	/*
	 * 查询分页列表，自定义分页语句及排序语句
	 */
	@Deprecated
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			String pagesql,Map<String,Object> paramMap,String orderBy){
		pb.setTotal(queryForInt(pagesql, paramMap));
		StringBuilder sb = new StringBuilder(sql);
		ot.addPageSql(sb, pb, orderBy);
		pb.setRows(query(sb.toString(), paramMap, ot));
		return  pb.getRows();
	}
	
	/*
	 * 分页自定义查询列表,自定义列
	 */
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			Map<String,Object> paramMap,RowMapper<T> rowMapper){
		String pagesql = createPageSql(sql);
		return  getPageListBySQL(pb,sql,pagesql, paramMap,null,rowMapper);
	}

	/*
	 * 分页自定义查询列表,自定义列
	 */
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
			Map<String,Object> paramMap,String orderBy,RowMapper<T> rowMapper){
		String pagesql = createPageSql(sql);
		return  getPageListBySQL(pb,sql,pagesql, paramMap,orderBy,rowMapper);
	}
	
	/*
	 * 查询列表,自定义列
	 * 自定义排序
	 * 自定义分页语句
	 */
	@Deprecated
	protected List<T> getPageListBySQL(PageBean<T> pb,String sql,
					String pagesql,Map<String,Object> paramMap,
				String orderBy,RowMapper<T> rowMapper){
		pb.setTotal(queryForInt(pagesql, paramMap));
		StringBuilder sb = new StringBuilder(sql);
		ot.addPageSql(sb, pb, orderBy);
		sql = sb.toString();
		pb.setRows(query(sql, paramMap, rowMapper));
		return  pb.getRows();
	}

/**********spring jdbc的封装****************/
	
	//sql语句添加mycat前缀
	protected String addPreSQL(String sql){
		return sql;
	}
		
	//spring jdbc支持
	protected <TT> TT queryForObject(String sql, Map<String, ?> paramMap, Class<TT> requiredType) {
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		return jdbcTemp.queryForObject(sql, paramMap, requiredType);
	}
	
	//spring jdbc支持,可以返回null
	protected <TT> TT queryForObjectWithNull(String sql, Map<String, ?> paramMap, Class<TT> requiredType) {
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		List<TT> tt = jdbcTemp.query(sql, paramMap, new RowMapper<TT>() {
			@SuppressWarnings("unchecked")
			@Override
			public TT mapRow(ResultSet rs, int arg1) throws SQLException {
				return (TT) rs.getObject(1);
			}
		});
		if(tt.size() == 0) return null;
		return tt.get(0);
	}
	
	//spring jdbc支持
	@Override
	public int update(String sql, Map<String, ?> paramMap) {
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		return jdbcTemp.update(sql, paramMap);
	}

	//spring jdbc支持
	protected int update(String sql) {
		sql = addPreSQL(sql);
		log.debug(sql);
		return jdbcTemp0.update(sql);
	}
	
	//spring jdbc支持
	protected List<T> query(String sql, Map<String, Object> paramMap, RowMapper<T> ot) {
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		return jdbcTemp.query(sql, paramMap, ot);
	}
	
	//查询结果map结构 ,即一条记录
	public T  queryForMap(String sql){
		List<T> resultMap = queryForList(sql);
		if(resultMap.isEmpty()) return null;
		return resultMap.get(0);
	}
		
	//查询结果map结构 ,即一条记录
	public T  queryForMap(String sql, Map<String, Object> paramMap){
		List<T> resultMap = queryForList(sql,paramMap);
		if(resultMap.isEmpty()) return null;
		return resultMap.get(0);
	}
		
	//查询结果封装为map
	@SuppressWarnings("unchecked")
	@Override
	public List<T>  queryForList(String sql, Map<String, Object> paramMap){
		if(jdbcTemp == null) return null;
		if(null == paramMap) paramMap = new HashMap<>();
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		return (List<T>) jdbcTemp.queryForList(sql, paramMap);
	}
	
	//查询结果封装为map
	@Override
	public List<Map<String,Object>>  queryForListMap(String sql, Map<String, Object> paramMap){
		if(jdbcTemp == null) return null;
		if(null == paramMap) paramMap = new HashMap<>();
		sql = addPreSQL(sql);
		log.debug(sql);
		log.debug(paramMap);
		return jdbcTemp.queryForList(sql, paramMap);
	}
		
	//查询结果封装为map
	@Override
	public List<T>  queryForList(String sql){
		return queryForList(sql, new MapBuilder().build());
	}
		
	//分页
	@Deprecated
	public List<T>  queryPageForList(PageBean<T>pb,String sql,
				String pageSql,Map<String, Object> paramMap){
		return  queryPageForList(pb,sql,pageSql,paramMap,null);
	}
		
	//分页并自定义排序
	@Deprecated
	public List<T>  queryPageForList(PageBean<T>pb,String sql,
					String pageSql,Map<String, Object> paramMap,String orderBy){
		pb.setTotal(queryForInt(pageSql, paramMap));
		StringBuilder sb = new StringBuilder(sql);
		ot.addPageSql(sb, pb, orderBy);
		sql = sb.toString();
		pb.setRows((List<T>)queryForList(sql, paramMap));
		return  pb.getRows();	
	}
	
	//实体转换接口的封装
	protected T mapRow(ResultSet arg0, int arg1) throws SQLException{
		if(ot == null) return null;
		return ot.mapRow(arg0, arg1);
	}
	
	@SuppressWarnings("deprecation")
	protected T mapRowFilterIn(ResultSet arg0, int arg1,String fields) throws SQLException{
		return ot.mapRowFilterIn(arg0, arg1,fields);
	}
	
	@SuppressWarnings("deprecation")
	protected T mapRowFilterEx(ResultSet arg0, int arg1,String fields) throws SQLException{
		return ot.mapRowFilterEx(arg0, arg1,fields);
	}
	
	
	public NamedParameterJdbcTemplate getJdbcTemp() {
		return jdbcTemp;
	}

	public JdbcTemplate getJdbcTemp0() {
		return jdbcTemp0;
	}
	
	public void setJdbcTemp(NamedParameterJdbcTemplate jdbcTemp) {
		this.jdbcTemp = jdbcTemp;
	}

	public void setJdbcTemp0(JdbcTemplate jdbcTemp0) {
		this.jdbcTemp0 = jdbcTemp0;
	}
	public OrmDBType getOrmdb() {
		return ormdb;
	}

}
